package com.clickhouse.sample.jdbc.dao;


import com.clickhouse.sample.jdbc.persistence.ClickHouseColumn;
import com.clickhouse.sample.jdbc.persistence.ClickHouseTable;
import org.apache.http.client.utils.DateUtils;
import org.springframework.beans.factory.annotation.Autowired;
import ru.yandex.clickhouse.ClickHouseDataSource;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

/**
 * ClickHouse Dao层基类
 * @author chenjiacheng
 * @CREATE 20190628 10:45
 */
public abstract class AbstractClickHouseDao<T> {

    @Autowired
    protected ClickHouseDataSource clickHouseDataSource;

    public AbstractClickHouseDao(){}


    public AbstractClickHouseDao( ClickHouseDataSource clickHouseDataSource ){
        this.clickHouseDataSource = clickHouseDataSource;
    }


    private Class<T> tClass;
    private Field[] allFieldList;
    // key-->column_name, value-->field_name
    private Map<String,Field> allFieldMap;
    private Lock allLock = new ReentrantLock();

    private final static String CONDITION_EQUAL = "equal";
    private final static String CONDITION_LIKE = "like";


    private Connection connection;


    private Connection getConnection(){
        try {
            if( connection == null || connection.isClosed() ) {
                connection = clickHouseDataSource.getConnection();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException( e );
        }
        return connection;
    }

    /**
     * 获取T的Class实例 以便后续复用，提高性能
     *
     * @return
     */
    private Class<T> getTClass(){
        if (null == tClass) {
            tClass = (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
        }
        return tClass;
    }

    /**
     * 获取所有的属性数组，以便后续复用，提高性能
     *
     * @return
     */
    private Field[] getAllFieldList() {
        if (null == allFieldList) {
            allLock.lock();
            try {
                if (null == allFieldList) {
                    allFieldList = getTClass().getDeclaredFields();
                }
            }catch ( Exception e ){
                e.printStackTrace();
            }finally {
                allLock.unlock();
            }
        }
        return allFieldList;
    }

    /**
     * 获取
     *
     * @return
     */
    private Map<String,Field> getAllFieldMap(){
        if( null == allFieldMap ){
            Field[] fields = getAllFieldList();
            allFieldMap = new HashMap<String, Field>( fields.length );
            for( Field field : fields ){
                ClickHouseColumn column = field.getAnnotation( ClickHouseColumn.class );
                allFieldMap.put( column.name(), field );
            }
        }
        return allFieldMap;
    }


    /**
     * 获取当前dao对应的实体类对应的表名，如果不包含则报错
     *
     * @param tClass
     * @return
     */
    private String getTableName( Class<T> tClass ){
        ClickHouseTable table = tClass.getAnnotation( ClickHouseTable.class );
        if( table == null ){
            throw new RuntimeException(" no Annotation 'javax.persistence.Table' in clazz  ");
        }else{
            return table.name();
        }
    }

    /**
     * 通过反射调用某个属性的get方法获取值
     *
     * @param tClass
     * @param fieldName
     * @param t
     * @return
     */
    private Object getMethodGetResult(Class<T> tClass, String fieldName, T t) {
        // 仅对Column注解的字段生效
        try {
            return tClass.getMethod("get" + capitalize(fieldName)).invoke(t);
        } catch (Exception e) {
            // 把这个异常转为运行时异常再抛出
            throw new RuntimeException("tClazz=" + tClass.getName(), e);
        }
    }

    /**
     * 通过反射设置值
     *
     * @param t
     * @param fieldName
     * @param value
     */
    private void setMethodSetResult( Class<T>  clazz, T t, String fieldName, Class<?> valueType,  Object value) {
        // 仅对Column注解的字段生效
        try {
            Method method = clazz.getMethod("set" + capitalize(fieldName), valueType);

            method.invoke( t, valueType.cast(value) );

        } catch (Exception e) {
            e.printStackTrace();
            // 把这个异常转为运行时异常再抛出
            throw new RuntimeException("tClazz=" + tClass.getName(), e);
        }
    }

    /**
     * 批量保存数据
     * @param dataList
     */
    public void batchInsert( List<T> dataList  ){

        if( dataList == null || dataList.size() < 1 ){
            throw new RuntimeException(" dataList id null ");
        }

        Class<T> clazz = getTClass();

        // 构建插入语句
        StringBuilder sql = new StringBuilder( " insert into " );

        // 构建值占位符
        StringBuilder values = new StringBuilder();

        // 获得实体类的Table注解，用来确定数据库表
        sql.append( getTableName( tClass ) );


        sql.append("(");
        values.append("(");

        // 获取类的所有字段
        Field[] fields = getAllFieldList();
        for(int i = 0; i < fields.length; i++) {
            ClickHouseColumn column = fields[i].getAnnotation( ClickHouseColumn.class );
            if(column != null) {
                sql.append( column.name() ).append(",");
                values.append("?").append(",");
            }
        }

        sql.deleteCharAt( sql.length() - 1 );
        values.deleteCharAt( values.length() - 1 );

        sql.append(")");
        values.append(")");
        sql.append( " values " ).append( values );

        // 执行批量插入操作
        try {
            Connection connection = getConnection();
            connection.setAutoCommit( false );
            PreparedStatement ps = connection.prepareStatement( sql.toString() );
            for(  T data : dataList  ){
                for(int i = 0; i < fields.length; i++) {


                    Object value = clazz.getMethod("get" + capitalize( fields[i].getName())).invoke( data );
                    // 日期特殊处理
                    if( fields[i].getType().getName().indexOf( "java.util.Date" ) != -1 ){
                        ClickHouseColumn column = fields[i].getAnnotation( ClickHouseColumn.class );
                        String columnDefined = column.format();
                        if( columnDefined == null ){
                            columnDefined = "yyyy-MM-dd HH:mm:ss";
                        }
                        if( value != null ){
                            Date date = (Date) value;
                            value = DateUtils.formatDate( date, columnDefined );
                        }
                        else{
                            value = DateUtils.formatDate( new Date(), columnDefined );
                        }
                    }
                    ps.setObject( i+1, value );

                }
                //  添加到批次
                ps.addBatch();
            }
            //  提交批处理
            ps.executeBatch();
            // 执行
            connection.commit();

        }catch ( Exception e ){
            throw new RuntimeException(e);
//            e.printStackTrace();
        }
    }

    /**
     * 获取所有记录
     *
     * @return
     */
    public List<T> selectList(){
        Class<T> clazz = getTClass();
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from ").append( getTableName( clazz ) );
        ResultSet resultSet = executeQuery( sql.toString() );
        return convertSetToEntity( resultSet, clazz );
    }

    /**
     * 获取所有记录，并进行排序
     *
     * @param orderBy
     * @return
     */
    public List<T> selectList( String orderBy ){
        Class<T> clazz = getTClass();

        StringBuilder sql = new StringBuilder();
        sql.append(" select * from ").append( getTableName( clazz ) );
        sql.append(" order by ").append( orderBy );
        ResultSet resultSet = executeQuery( sql.toString() );
        return convertSetToEntity( resultSet, clazz );
    }


    /**
     * 根据当前实体构建条件
     *
     * @param entity    当为null，则获取全部数据
     * @return
     */
    public List<T> selectList( T entity ){
        return selectList( entity, null );
    }

    /**
     * 根据当前实体构建条件，并进行排序
     *
     * @param entity
     * @param orderBy
     * @return
     */
    public List<T> selectList( T entity, String orderBy ){

        if( entity == null ){
            return selectList();
        }
        Class<T> clazz = getTClass();
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from ").append( getTableName( clazz ) );
        sql.append(" where ");

        ConditionEntity conditionEntity = getWhereAndSqlAndValue( getTClass(), entity, CONDITION_EQUAL );
        sql.append( conditionEntity.getSql() );

        if( orderBy != null ){
            sql.append( " order by " ).append( orderBy );
        }

        try {
            PreparedStatement preparedStatement = createPreparedStatement( sql.toString(), conditionEntity.getValues() );
            ResultSet resultSet = preparedStatement.executeQuery();
            return convertSetToEntity( resultSet, clazz );
        }catch ( Exception e ){

            e.printStackTrace();
        }
        return new ArrayList<T>( 0 );
    }

    /**
     * 获取分页数据记录
     *
     * @param start
     * @param size
     * @return
     */
    public List<T> selectPage( long start, long size ){
        Class<T> clazz = getTClass();
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from ").append( getTableName( clazz ) );
        sql.append( " limit " ).append( start ).append(",").append( size );
        ResultSet resultSet = executeQuery( sql.toString() );
        return convertSetToEntity( resultSet, clazz );
    }

    /**
     * 获取分页数据记录
     *
     * @param entity
     * @param start
     * @param size
     * @return
     */
    public List<T> selectPage( T entity, long start, long size ){
        return selectPage( entity, null, start, size );
    }

    public List<T> selectPage( String orderBy, long start, long size ){
        Class<T> clazz = getTClass();
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from ").append( getTableName( clazz ) );
        if( orderBy != null ){
            sql.append( " order by " ).append( orderBy );
        }
        sql.append( " limit " ).append( start ).append(",").append( size );
        ResultSet resultSet = executeQuery( sql.toString() );
        return convertSetToEntity( resultSet, clazz );
    }


    /**
     * 获取分页数据记录
     *
     * @param entity
     * @param orderBy
     * @param start
     * @param size
     * @return
     */
    public List<T> selectPage( T entity, String orderBy, long start, long size ){
        if( entity == null ){
            return selectList();
        }
        Class<T> clazz = getTClass();
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from ").append( getTableName( clazz ) );

        sql.append(" where ");

        ConditionEntity conditionEntity = getWhereAndSqlAndValue( getTClass(), entity, CONDITION_EQUAL );
        sql.append( conditionEntity.getSql() );

        if( orderBy != null ){
            sql.append( " order by " ).append( orderBy );
        }

        sql.append( " limit " ).append( start ).append(",").append( size );

        try {
            PreparedStatement preparedStatement = createPreparedStatement( sql.toString(), conditionEntity.getValues() );
            ResultSet resultSet = preparedStatement.executeQuery();
            return convertSetToEntity( resultSet, clazz );
        }catch ( Exception e ){
            e.printStackTrace();
        }
        return new ArrayList<T>( 0 );
    }

    private PreparedStatement createPreparedStatement( String sql, Object[] values ) throws SQLException {
        PreparedStatement preparedStatement = getConnection().prepareStatement( sql.toString() );
        for( int i = 0; i <  values.length; i++  ){
            preparedStatement.setObject( i+1, values[i] );
        }
        return preparedStatement;
    }

    /**
     * 获取记录的数量
     *
     * @return
     */
    public int selectCount(){
        Class<T> clazz = getTClass();
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) as count from ").append( getTableName( clazz ) );
        ResultSet resultSet = executeQuery( sql.toString() );
        try {
            if( resultSet.next() ){
                return resultSet.getInt("count");
            }

        }catch ( Exception e ){
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 根据当前实体构建条件，获取过滤后的数据量
     *
     * @param entity
     * @return
     */
    public int selectCount( T entity ){

        if( entity == null ){
            return selectCount();
        }
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) as count from ").append( getTableName( getTClass() ) );
        sql.append(" where ");

        ConditionEntity conditionEntity = getWhereAndSqlAndValue( getTClass(), entity, CONDITION_EQUAL );
        sql.append( conditionEntity.getSql() );

        try {
            PreparedStatement preparedStatement = getConnection().prepareStatement( sql.toString() );
            Object[] values = conditionEntity.getValues();
            for( int i = 0; i <  values.length; i++  ){
                preparedStatement.setObject( i+1, values[i] );
            }

            ResultSet resultSet = preparedStatement.executeQuery();
            if( resultSet.next() ){
                return resultSet.getInt("count");
            }
        }catch ( Exception e ){
            e.printStackTrace();
        }
        return 0;
    }


    /**
     * 根据当前实体，构建数据条件
     *
     * @param clazz
     * @param t
     * @param condition
     * @return
     */
    private ConditionEntity getWhereAndSqlAndValue( Class<T> clazz, T t, String condition ){
        // 获取所有字段属性
        Field[] fields = getAllFieldList();

        // 创建一些容器用于后续运算
        StringBuilder sb = new StringBuilder();
        List<Object> values = new ArrayList<Object>();

        // 只针对Column注解列做处理
        for (int i = 0; i < fields.length; i++) {
            // 仅对Column注解的字段生效
            ClickHouseColumn column = fields[i].getAnnotation( ClickHouseColumn.class );
            if (column != null) {
                // 仅对字段内容不为null生效
                Object object = getMethodGetResult( tClass, fields[i].getName(), t );
                if (null != object) {
                    if ( CONDITION_EQUAL.equals( condition ) ) {
                        sb.append(" " + column.name() + " = ? and");
                        values.add(object);
                    } else if ( CONDITION_LIKE.equals( condition ) ) {
                        sb.append(" " + column.name() + " like ? and");
                        values.add("%" + object + "%");
                    }
                }
            }
        }

        // 获取最终sql
        String sql = sb.toString();
        if (sql.endsWith("and")) {
            sql = sql.substring(0, (sql.length() - 3));
        }

        // 返回结果
        ConditionEntity conditionEntity = new ConditionEntity();
        conditionEntity.setSql( sql );
        conditionEntity.setValues( values.toArray() );
        return conditionEntity;
    }

    /**
     * 将ResultSet结果集装到List对象
     *
     * @param resultSet
     * @param clazz
     * @return
     */
    private List<T> convertSetToEntity( ResultSet resultSet, Class<T> clazz ){

        List<T> result = new ArrayList<T>( );
        Map<String,Field> fieldMap = getAllFieldMap();
        try {
            while ( resultSet.next() ){
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                T entity = clazz.newInstance();

                for( int i = 1 ; i <= resultSetMetaData.getColumnCount() ; i++ ){
                    String columnName = resultSetMetaData.getColumnName( i );
                    if(  fieldMap.containsKey( columnName ) ){
                        Field field = fieldMap.get( columnName );
                        Class<?> type = field.getType();
                        String fieldName = field.getName();
                        setMethodSetResult( getTClass(), entity, fieldName, type, resultSet.getObject( i ) );
                    }
                }

                result.add( entity );
            }

        }catch ( Exception e ){
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 执行查询语句
     * @param sql       查询语句
     * @return
     */
    public List<Map<String,Object>> query( String sql ){
        List<Map<String,Object>> result = new ArrayList<Map<String, Object>>();

        ResultSet results = executeQuery( sql );
        if( results != null ){
            try {
                ResultSetMetaData resultSetMetaData =  results.getMetaData();
                while( results.next() ) {
                    Map<String, Object> map = new HashMap<String, Object>();
                    for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {

                        map.put(
                                resultSetMetaData.getColumnName(i),
                                results.getObject(i)
                        );
                    }
                    result.add(map);
                }

            }catch ( Exception e ){
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 执行查询语句
     * @param sql       查询语句
     * @return
     */
    public ResultSet executeQuery( String sql ){
        Connection connection = getConnection();
        Statement statement = null;
        ResultSet results = null;
        try {
            statement = connection.createStatement();
            results = statement.executeQuery( sql );
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            statement = null;
        }
        return results;
    }


    /**
     * 将首字母变成大写
     * @param str
     * @return
     */
    private String capitalize(final String str) {
        int strLen;
        if (str == null || (strLen = str.length()) == 0) {
            return str;
        }

        final int firstCodePoint = str.codePointAt(0);
        final int newCodePoint = Character.toTitleCase(firstCodePoint);
        if (firstCodePoint == newCodePoint) {
            // already capitalized
            return str;
        }

        final int newCodePoints[] = new int[strLen]; // cannot be longer than the char array
        int outOffset = 0;
        newCodePoints[outOffset++] = newCodePoint; // copy the first codepoint
        for (int inOffset = Character.charCount(firstCodePoint); inOffset < strLen; ) {
            final int codePoint = str.codePointAt(inOffset);
            newCodePoints[outOffset++] = codePoint; // copy the remaining ones
            inOffset += Character.charCount(codePoint);
        }
        return new String(newCodePoints, 0, outOffset);
    }


    private class ConditionEntity{
        private String sql;

        private Object[] values;

        public String getSql() {
            return sql;
        }

        public void setSql(String sql) {
            this.sql = sql;
        }

        public Object[] getValues() {
            return values;
        }

        public void setValues(Object[] values) {
            this.values = values;
        }
    }


}
